package ade.prime.database ;

import java.util.ArrayList;
import java.util.List;

import ade.prime.exception.AAAInternalException;
import android.text.TextUtils;

/**
 * Interface contenant les noms des tables ainsi que leur colonnes (nom et type SQL).
 * 
 * @author		Schnell Michaël, Zanette Sébastien
 * @version		1.0
 */
public interface DatabaseConstants {
	
	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *\
	 *                                                                                   *
	 * 		Données générales concernant la base de données                              *
	 *                                                                                   * 
	\* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

	/**
	 * Version courante de la base de données.
	 */
	public static final int VERSION_BDD = 2  ;
	
	/**
	 * Nom du fichier contenant la base de données.
	 */
	public static final String NOM_BDD = "ADEWEBLITE.db" ; //$NON-NLS-1$
	
	/**
	 * Indice utilisé pour indiquer une colonne inconnue dans la table.
	 */
	public static final int UNKOWN_COLUMN_INDEX = -1 ;
	
	/**
	 * Date format.
	 */
	public static final String DATE_FORMAT = "yyyy/MM/dd HH:mm" ; //$NON-NLS-1$
	
	/**
	 * Partie where utilisée pour accéder à un élément via son rowid.
	 */
	public static final String WHERE_ROWID = "rowid = %s" ; //$NON-NLS-1$
	
	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *\
	 *                                                                                   *
	 * 		Table Schedule (Contient les informations sur les emplois du temps)          *
	 *                                                                                   * 
	\* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
	
	public static final String TABLE_SCHEDULE_NAME = "Schedule" ; //$NON-NLS-1$

	public static final String TABLE_SCHEDULE_COL_NAME_ID = 
			"_id" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_NAME_NAME = 
			"name" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_NAME_PROJECT_ID = 
			"project_id" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_NAME_RESOURCES = 
			"resources" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_NAME_STUDENT_NUM = 
			"student_number" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_NAME_SUB_PERIOD = 
			"period" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_NAME_LAST_UPDATE = 
			"last_update" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_NAME_STATUS = 
			"status" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_NAME_CREATED = 
			"created" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_NAME_NOTIFY_UPDATE = 
			"notify_update" ; //$NON-NLS-1$

	public static final String TABLE_SCHEDULE_COL_TYPE_ID = 
			"INTEGER PRIMARY KEY AUTOINCREMENT" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_TYPE_NAME = 
			"VARCHAR(100) UNIQUE" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_TYPE_PROJECT_ID = 
			"INTEGER NOT NULL" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_TYPE_RESOURCES = 
			"TEXT NOT NULL" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_TYPE_STUDENT_NUM = 
			"TEXT NULL" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_TYPE_SUB_PERIOD = 
			"INTEGER NOT NULL" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_TYPE_LAST_UPDATE = 
			"DATETIME NULL" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_TYPE_STATUS = 
			"INTEGER NOT NULL" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_TYPE_CREATED = 
			"DATETIME NOT NULL" ; //$NON-NLS-1$
	public static final String TABLE_SCHEDULE_COL_TYPE_NOTIFY_UPDATE = 
			"INTEGER NOT NULL" ; //$NON-NLS-1$
	
	public static final SQLTable TABLE_SCHEDULE = new SQLTable(
			TABLE_SCHEDULE_NAME,
			new String[]{
					TABLE_SCHEDULE_COL_NAME_ID,
					TABLE_SCHEDULE_COL_NAME_NAME,
					TABLE_SCHEDULE_COL_NAME_PROJECT_ID,
					TABLE_SCHEDULE_COL_NAME_RESOURCES,
					TABLE_SCHEDULE_COL_NAME_STUDENT_NUM,
					TABLE_SCHEDULE_COL_NAME_SUB_PERIOD,
					TABLE_SCHEDULE_COL_NAME_LAST_UPDATE,
					TABLE_SCHEDULE_COL_NAME_STATUS,
					TABLE_SCHEDULE_COL_NAME_CREATED,
					TABLE_SCHEDULE_COL_NAME_NOTIFY_UPDATE
			},
			new String[]{
					TABLE_SCHEDULE_COL_TYPE_ID,
					TABLE_SCHEDULE_COL_TYPE_NAME,
					TABLE_SCHEDULE_COL_TYPE_PROJECT_ID,
					TABLE_SCHEDULE_COL_TYPE_RESOURCES,
					TABLE_SCHEDULE_COL_TYPE_STUDENT_NUM,
					TABLE_SCHEDULE_COL_TYPE_SUB_PERIOD,
					TABLE_SCHEDULE_COL_TYPE_LAST_UPDATE,
					TABLE_SCHEDULE_COL_TYPE_STATUS,
					TABLE_SCHEDULE_COL_TYPE_CREATED,
					TABLE_SCHEDULE_COL_TYPE_NOTIFY_UPDATE
			}
	) ;

	public static final Integer SCHEDULE_NOTIFY_TRUE  = Integer.valueOf(1) ;
	public static final Integer SCHEDULE_NOTIFY_FALSE = Integer.valueOf(0) ;
	
	public static final String TABLE_SCHEDULE_WHERE_ID =
			String.format("%s = %%s", TABLE_SCHEDULE_COL_NAME_ID) ; //$NON-NLS-1$
	
	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *\
	 *                                                                                   *
	 * 		Table Lesson (contient les informations sur les cours d'un emploi du temps)  *
	 *                                                                                   * 
	\* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
	
	public static final String TABLE_LESSON_NAME = "Lesson" ; //$NON-NLS-1$
	
	public static final String TABLE_LESSON_COL_NAME_ID = 
			"_id" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_NAME_TITLE = 
			"title" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_NAME_TIME = 
			"time" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_NAME_SCHEDULE = 
			"schedule" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_NAME_TYPE = 
			"type" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_NAME_CHANGE = 
			"change" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_NAME_CREATED = 
			"created" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_NAME_ROOMS = 
			"rooms" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_NAME_GROUPS = 
			"groups" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_NAME_TEACHERS = 
			"teachers" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_NAME_COLOR = 
			"color" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_NAME_NOTE = 
			"note" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_NAME_GUID = 
			"guid" ; //$NON-NLS-1$
	
	public static final String TABLE_LESSON_COL_TYPE_ID = 
			"INTEGER PRIMARY KEY AUTOINCREMENT" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_TYPE_TITLE = 
			"VARCHAR(100) NOT NULL" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_TYPE_TIME = 
			"DATETIME NOT NULL" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_TYPE_SCHEDULE = 
			"INTEGER NOT NULL" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_TYPE_TYPE = 
			"INTEGER NOT NULL" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_TYPE_CHANGE = 
			"INTEGER NOT NULL" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_TYPE_CREATED = 
			"DATETIME NOT NULL" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_TYPE_ROOMS = 
			"VARCHAR(100) NULL" ;     //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_TYPE_GROUPS = 
			"VARCHAR(100) NULL" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_TYPE_TEACHERS = 
			"VARCHAR(100) NULL" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_TYPE_COLOR = 
			"VARCHAR(6) NOT NULL" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_TYPE_NOTE = 
			"VARCHAR(100) NULL" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_COL_TYPE_GUID = 
			"VARCHAR(10) NULL" ; //$NON-NLS-1$
	
	public static final SQLTable TABLE_LESSON = new SQLTable(
			TABLE_LESSON_NAME,
			new String[]{
					TABLE_LESSON_COL_NAME_ID,
					TABLE_LESSON_COL_NAME_TITLE,
					TABLE_LESSON_COL_NAME_TIME,
					TABLE_LESSON_COL_NAME_SCHEDULE,
					TABLE_LESSON_COL_NAME_TYPE,
					TABLE_LESSON_COL_NAME_CHANGE,
					TABLE_LESSON_COL_NAME_CREATED,
					TABLE_LESSON_COL_NAME_ROOMS,
					TABLE_LESSON_COL_NAME_GROUPS,
					TABLE_LESSON_COL_NAME_TEACHERS,
					TABLE_LESSON_COL_NAME_COLOR,
					TABLE_LESSON_COL_NAME_NOTE,
					TABLE_LESSON_COL_NAME_GUID
			},
			new String[]{
					TABLE_LESSON_COL_TYPE_ID,
					TABLE_LESSON_COL_TYPE_TITLE,
					TABLE_LESSON_COL_TYPE_TIME,
					TABLE_LESSON_COL_TYPE_SCHEDULE,
					TABLE_LESSON_COL_TYPE_TYPE,
					TABLE_LESSON_COL_TYPE_CHANGE,
					TABLE_LESSON_COL_TYPE_CREATED,
					TABLE_LESSON_COL_TYPE_ROOMS,
					TABLE_LESSON_COL_TYPE_GROUPS,
					TABLE_LESSON_COL_TYPE_TEACHERS,
					TABLE_LESSON_COL_TYPE_COLOR,
					TABLE_LESSON_COL_TYPE_NOTE,
					TABLE_LESSON_COL_TYPE_GUID
			}
	) ;
	
	public static final String TABLE_LESSON_WHERE_SCHEDULE =
			String.format("%s = %%s", TABLE_LESSON_COL_NAME_SCHEDULE) ; //$NON-NLS-1$
	
	public static final String TABLE_LESSON_WHERE_SCHEDULE_AND_FUTURE =
			String.format("%s = %%s AND (substr(%2$s, 0, 11) || substr(%2$s, -5) >= strftime('%%%%Y/%%%%m/%%%%d%%%%H:%%%%M', 'now', 'localtime'))", //$NON-NLS-1$ 
					TABLE_LESSON_COL_NAME_SCHEDULE, TABLE_LESSON_COL_NAME_TIME) ;
	
	public static final String TABLE_LESSON_WHERE_ID_IN =
			String.format("%s IN (%%s)", TABLE_LESSON_COL_NAME_ID) ;//$NON-NLS-1$
	
	public static final String TABLE_LESSON_WHERE_ID =
			String.format("%s = %%s", TABLE_LESSON_COL_NAME_ID) ; //$NON-NLS-1$
	
	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *\
	 *                                                                                   *
	 * 		Table LessonHistory (contient les informations passées sur les cours         *
	 *                           d'un emploi du temps)                                   *
	 *                                                                                   * 
	\* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
	
	public static final String TABLE_LESSON_HISTORY_NAME = 
			"LessonHistory" ; //$NON-NLS-1$
	
	public static final String TABLE_LESSON_HISTORY_PREFIX = "history_" ; //$NON-NLS-1$
	
	public static final String TABLE_LESSON_HISTORY_COL_NAME_LESSON_ID = 
			TextUtils.concat(
					TABLE_LESSON_HISTORY_PREFIX, TABLE_LESSON_COL_NAME_ID).toString() ;
	public static final String TABLE_LESSON_HISTORY_COL_NAME_TIME = 
			TextUtils.concat(
					TABLE_LESSON_HISTORY_PREFIX, TABLE_LESSON_COL_NAME_TIME).toString() ;
	public static final String TABLE_LESSON_HISTORY_COL_NAME_ROOMS = 
			TextUtils.concat(
					TABLE_LESSON_HISTORY_PREFIX, TABLE_LESSON_COL_NAME_ROOMS).toString() ;
	
	public static final String TABLE_LESSON_HISTORY_COL_TYPE_LESSON_ID = 
			"INTEGER PRIMARY KEY" ; //$NON-NLS-1$
	public static final String TABLE_LESSON_HISTORY_COL_TYPE_TIME = 
			TABLE_LESSON_COL_TYPE_TIME ;
	public static final String TABLE_LESSON_HISTORY_COL_TYPE_ROOMS = 
			TABLE_LESSON_COL_TYPE_ROOMS ;
	
	public static final SQLTable TABLE_LESSON_HISTORY = new SQLTable(
			TABLE_LESSON_HISTORY_NAME,
			new String[]{
					TABLE_LESSON_HISTORY_COL_NAME_LESSON_ID,
					TABLE_LESSON_HISTORY_COL_NAME_TIME,
					TABLE_LESSON_HISTORY_COL_NAME_ROOMS
			},
			new String[]{
					TABLE_LESSON_HISTORY_COL_TYPE_LESSON_ID,
					TABLE_LESSON_HISTORY_COL_TYPE_TIME,
					TABLE_LESSON_HISTORY_COL_TYPE_ROOMS
			}
	) ;
	
	public static final String TABLE_LESSON_HISTORY_WHERE_ID = 
			String.format("%s = %%s", TABLE_LESSON_HISTORY_COL_NAME_LESSON_ID) ; //$NON-NLS-1$
	
	public static final String TABLE_LESSON_HISTORY_WHERE_ID_IN =
			String.format("%s IN (%%s)", TABLE_LESSON_HISTORY_COL_NAME_LESSON_ID) ; //$NON-NLS-1$
	
	public static final String TABLE_LESSON_HISTORY_WHERE_SCHEDULE =
			String.format(TABLE_LESSON_HISTORY_WHERE_ID_IN, 
					String.format("SELECT %s FROM %s WHERE %s", //$NON-NLS-1$
							TABLE_LESSON_COL_NAME_ID,
							TABLE_LESSON_NAME,
							TABLE_LESSON_WHERE_SCHEDULE)) ;

	public static final String VIEW_LESSON_WITH_HISTORY_NAME =
			"LessonWithHistory" ; //$NON-NLS-1$

	public static final String VIEW_LESSON_WITH_HISTORY_WHERE =
			"%s.%s = %s.%s" ; //$NON-NLS-1$
	
	public static final SQLView VIEW_LESSON_WITH_HISTORY = new SQLView(
			VIEW_LESSON_WITH_HISTORY_NAME,
			String.format(VIEW_LESSON_WITH_HISTORY_WHERE, 
					TABLE_LESSON_NAME, TABLE_LESSON_COL_NAME_ID, 
					TABLE_LESSON_HISTORY_NAME, TABLE_LESSON_HISTORY_COL_NAME_LESSON_ID), 
			TABLE_LESSON, TABLE_LESSON_HISTORY
			) {
		/**
		 * {@inheritDoc}
		 */
		@Override
		public String getCreateViewSQL() {
			StringBuilder createRequest = new StringBuilder() ;
			createRequest.append(String.format(CREATE_VIEW_STMT_START, this.name)) ;
			
			int nColumns = this.columns.length ;
			for(int col=0; col < nColumns; ++col) {
				String tableName = this.columns[col].getTableName() ;
				String origName  = this.columns[col].getOriginalName() ;
				String colName   = this.columns[col].getName() ;
				createRequest.append(String.format(CREATE_VIEW_STMT_COLUMN_DEF,
						tableName, origName, colName)) ;
				createRequest.append(CREATE_VIEW_STMT_COLUMN_DEF_SEPARATOR) ;
			}// while
			int requestLength = createRequest.length() ;
			createRequest.delete(
					requestLength - CREATE_VIEW_STMT_COLUMN_DEF_SEPARATOR.length(), 
					requestLength) ;
			
			String from = String.format(CREATE_VIEW_FROM_STMT,  TABLE_LESSON_NAME) ;
			createRequest.append(from) ;
			
			String join = String.format("LEFT JOIN %s ON %s ",  //$NON-NLS-1$
					TABLE_LESSON_HISTORY_NAME, this.whereStmt) ;
			createRequest.append(join) ;
			
			createRequest.append(CREATE_VIEW_STMT_END) ;
			return createRequest.toString() ;
		}// getCreateViewSQL()
	} ;
	
	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *\
	 *                                                                                   *
	 * 		Table StudentNumber (contient les informations sur les numéros étudiants)    *
	 *                                                                                   * 
	\* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
	
	public static final String TABLE_STUDENT_NUMBER_NAME = 
			"StudentNumber" ; //$NON-NLS-1$
	
	public static final String TABLE_STUDENT_NUMBER_COL_NAME_STUDENT_NUMBER = 
			"student_number" ; //$NON-NLS-1$
	public static final String TABLE_STUDENT_NUMBER_COL_NAME_RESOURCES = 
			"resources" ; //$NON-NLS-1$
	
	public static final String TABLE_STUDENT_NUMBER_COL_TYPE_STUDENT_NUMBER = 
			"VARCHAR(30) PRIMARY KEY" ; //$NON-NLS-1$
	public static final String TABLE_STUDENT_NUMBER_COL_TYPE_RESOURCES = 
			"VARCHAR(100) NOT NULL" ; //$NON-NLS-1$
	
	public static final SQLTable TABLE_STUDENT_NUMBER = new SQLTable(
			TABLE_STUDENT_NUMBER_NAME,
			new String[]{
					TABLE_STUDENT_NUMBER_COL_NAME_STUDENT_NUMBER,
					TABLE_STUDENT_NUMBER_COL_NAME_RESOURCES
			},
			new String[]{
					TABLE_STUDENT_NUMBER_COL_TYPE_STUDENT_NUMBER,
					TABLE_STUDENT_NUMBER_COL_TYPE_RESOURCES
			}
	) ;
	
	public static final String TABLE_STUDENT_NUMBER_WHERE_NUMBER =
			String.format("%s = '%%s'", TABLE_STUDENT_NUMBER_COL_NAME_STUDENT_NUMBER) ; //$NON-NLS-1$
	
	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *\
	 *                                                                                   *
	 * 		Outils auxiliaires                                                           *
	 *                                                                                   * 
	\* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
	
	/**
	 * Classe utilisée pour représenter une table SQL dans la base de données.
	 * 
	 * @author		Schnell Michaël
	 * @version		1.0
	 */
	public static class SQLTable {
		
		/**
		 * Début d'une requête SQL de création d'un table.
		 * 
		 * @param	name
		 * 			name of the SQL table
		 */
		protected static final String CREATE_TABLE_STMT_START =
				"CREATE TABLE IF NOT EXISTS %s (" ; //$NON-NLS-1$
		
		/**
		 * Définition d'une colonne lors d'une requête SQL de création d'un table.
		 * 
		 * @param	name
		 * 			name of the SQL column
		 * 
		 * @param	type
		 * 			type and constraints of the SQL column
		 */
		protected static final String CREATE_TABLE_STMT_COLUMN_DEF =
				"%s %s" ; //$NON-NLS-1$
		
		/**
		 * Séparateur pour les définitions de colonnes lors d'une requête SQL de 
		 * création d'un table.
		 */
		protected static final String CREATE_TABLE_STMT_COLUMN_DEF_SEPARATOR =
				"," ; //$NON-NLS-1$
		
		/**
		 * Fin d'une requête SQL de création d'un table.
		 */
		protected static final String CREATE_TABLE_STMT_END =
				");" ; //$NON-NLS-1$
		
		/**
		 * Nom de la table.
		 */
		protected String name ;
		
		/**
		 * Nom des colonnes de la table.
		 */
		protected String[] columnNames ;
		
		/**
		 * Type SQL des colonnes de la table.
		 */
		protected String[] columnTypes ;
		
		/**
		 * Construit une instance avec les valeurs fournies.
		 * 
		 * @param 	name
		 * 			nom de la table
		 * 
		 * @param 	columnNames
		 * 			nom des colonnes de la table
		 * 
		 * @param 	columnTypes
		 * 			type SQL des colonnes de la table
		 * 
		 * @throws 	AAAInternalException
		 * 			Si l'un des paramètres est {@code null}, les deux listes
		 * 			n'ont pas la même type ou l'une des listes contient {@code null}
		 */
		protected SQLTable(String name, String[] columnNames,
				String[] columnTypes) /*throws AAAInternalException*/ {
			super() ;
			this.name = name ;
			this.columnNames = columnNames ;
			this.columnTypes = columnTypes ;
		}// SQLTable(String, String[], String[])
		
		/**
		 * Retourne la requête SQLite3 utilisée pour créer la table représentée
		 * par cette instance.
		 * 
		 * @return	Requête SQLite3 utilisée pour créer la table représentée
		 * 			par cette instance
		 */
		public String getCreateTableSQL() {
			StringBuilder createRequest = new StringBuilder() ;
			createRequest.append(String.format(CREATE_TABLE_STMT_START, this.name)) ;
			
			int nColumns = this.columnNames.length ;
			for(int col=0; col < nColumns; ++col) {
				String colName = this.columnNames[col] ;
				String colType = this.columnTypes[col] ;
				createRequest.append(String.format(CREATE_TABLE_STMT_COLUMN_DEF,
						colName, colType)) ;
				createRequest.append(CREATE_TABLE_STMT_COLUMN_DEF_SEPARATOR) ;
			}// while
			int requestLength = createRequest.length() ;
			createRequest.delete(
					requestLength - CREATE_TABLE_STMT_COLUMN_DEF_SEPARATOR.length(), 
					requestLength) ;
			
			createRequest.append(CREATE_TABLE_STMT_END) ;
			return createRequest.toString() ;
		}// getCreateTableSQL()
		
		/**
		 * Retourne l'indice de la colonne demandée.
		 * 
		 * @param 	columnName
		 * 			nom de la colonne dont on recherche l'indice
		 * 
		 * @return	indice de la colonne recherchée ou
		 * 			{@code DatabaseConstants#UNKOWN_COLUMN_INDEX} si la colonne
		 * 			n'existe pas dans cette table
		 */
		public int getColumnIndex(String columnName) {
			int nColumns = this.columnNames.length ;
			int index = 0 ;
			while ((index < nColumns) 
					&& !this.columnNames[index].equals(columnName)) {
				index++ ;
			}// while
			if (index >= nColumns) {
				index = UNKOWN_COLUMN_INDEX ;
			}// if
			return index ;
		}// getColumnName(String)
		
		/**
		 * Retourne le nom de la table.
		 * 
		 * @return	Le nom de la table
		 */
		public String getName() {
			return this.name ;
		}// getName()
		
		/**
		 * Retourne la liste des noms des colonnes de la table.
		 * 
		 * @return	Retourne la liste des noms des colonnes de la table
		 */
		public String[] getColumnNames() {
			return this.columnNames ;
		}// getColumnNames()
 		
	}// class SQLTable
	
	/**
	 * Classe utilisée pour représenter une table SQL dans la base de données.
	 * 
	 * @author		Schnell Michaël
	 * @version		1.0
	 */
	public static class SQLView {
		
		/**
		 * Début d'une requête SQL de création d'une vue.
		 * 
		 * @param	name
		 * 			name of the SQL table
		 */
		protected static final String CREATE_VIEW_STMT_START =
				"CREATE VIEW IF NOT EXISTS %s AS SELECT " ; //$NON-NLS-1$
		
		/**
		 * Partie "from" de la requête de création d'une vue.
		 * 
		 * @param	names
		 * 			names of the SQL tables
		 */
		protected static final String CREATE_VIEW_FROM_STMT =
				" FROM %s " ; //$NON-NLS-1$
		
		/**
		 * Partie "where" de la requête de création d'une vue.
		 * 
		 * @param	cond
		 * 			join condition
		 */
		protected static final String CREATE_VIEW_WHERE_STMT =
				"WHERE %s " ; //$NON-NLS-1$
		
		/**
		 * Définition d'une colonne lors d'une requête SQL de création d'un table.
		 * 
		 * @param	table
		 * 			name of the SQL table containing the column
		 * 
		 * @param	column
		 * 			name of the SQL column
		 * 
		 * @param	name
		 * 			name of the column for the view
		 */
		protected static final String CREATE_VIEW_STMT_COLUMN_DEF =
				"%s.%s AS %s" ; //$NON-NLS-1$
		
		/**
		 * Séparateur pour les définitions de colonnes lors d'une requête SQL de 
		 * création d'un table.
		 */
		protected static final String CREATE_VIEW_STMT_COLUMN_DEF_SEPARATOR =
				"," ; //$NON-NLS-1$
		
		/**
		 * Fin d'une requête SQL de création d'un table.
		 */
		protected static final String CREATE_VIEW_STMT_END =
				";" ; //$NON-NLS-1$
		
		/**
		 * Nom de la vue.
		 */
		protected String name ;
		
		/**
		 * Colonnes de la vue.
		 */
		protected ViewColumn[] columns ;
		
		/**
		 * Partie "from" de la requete de création de la vue.
		 */
		protected String fromStmt ;
		
		/**
		 * Partie "where" de la requete de création de la vue.
		 */
		protected String whereStmt ;
		
		/**
		 * Construit une instance avec les valeurs fournies.
		 * 
		 * @param 	name
		 * 			nom de la vue
		 * 
		 * @param 	columns
		 * 			colonnes de la vue
		 * 
		 * @param 	from
		 * 			partie "from" de la requete de création de la vue (sans le mot
		 * 			clé from)
		 * 
		 * @param 	where
		 * 			partie "where" de la requete de création de la vue ({@code null}
		 * 			indique qu'il n'y a aucune condifition de jointure) (sans le mot
		 * 			clé where)
		 * 
		 * @throws 	AAAInternalException
		 * 			Si l'un des paramètres (sauf where) est {@code null} ou le 
		 * 			tableau de colonnes contient {@code null}
		 */
		protected SQLView(String name, ViewColumn[] columns, String from, String where)
			/*throws AAAInternalException*/ {
			super() ;
			this.name = name ;
			this.columns = columns ;
			this.fromStmt = from ;
			this.whereStmt = where ;
		}// SQLView(String, ViewColumn[], String, String)
		
		/**
		 * Construit une instance avec les valeurs fournies.
		 * 
		 * @param 	name
		 * 			nom de la vue
		 * 
		 * @param 	tables
		 * 			tables dont on prendra toutes les colonnes commes
		 * 			colonnes pour la vue
		 * 
		 * @param 	where
		 * 			partie "where" de la requete de création de la vue ({@code null}
		 * 			indique qu'il n'y a aucune condifition de jointure) (sans le mot
		 * 			clé SQL WHERE)
		 * 
		 * @throws 	AAAInternalException
		 * 			Si l'un des paramètres est {@code null}
		 */
		protected SQLView(String name, String where, SQLTable... tables)
			/*throws AAAInternalException*/ {
			super() ;
			this.name = name ;
			this.whereStmt = where ;
			
			List<ViewColumn> columnsList = new ArrayList<ViewColumn>() ;
			String[] tableNames = new String[tables.length] ;
			int index = 0 ;
			for(SQLTable table : tables) {
				String tableName = table.getName() ;
				tableNames[index] = tableName ;
				for(String columnName : table.getColumnNames()) {
					ViewColumn column = new ViewColumn(
							tableName, columnName, columnName) ;
					columnsList.add(column) ;
				}// for
				index++ ;
			}// for
			this.columns = columnsList.toArray(new ViewColumn[columnsList.size()]) ;
			this.fromStmt = String.format(CREATE_VIEW_FROM_STMT,
					TextUtils.join(CREATE_VIEW_STMT_COLUMN_DEF_SEPARATOR, tableNames)) ;
		}// SQLView(String, SQLTable...)
		
		/**
		 * Retourne la requête SQLite3 utilisée pour créer la vue représentée
		 * par cette instance.
		 * 
		 * @return	Requête SQLite3 utilisée pour créer la vue représentée
		 * 			par cette instance
		 */
		public String getCreateViewSQL() {
			StringBuilder createRequest = new StringBuilder() ;
			createRequest.append(String.format(CREATE_VIEW_STMT_START, this.name)) ;
			
			int nColumns = this.columns.length ;
			for(int col=0; col < nColumns; ++col) {
				String tableName = this.columns[col].getTableName() ;
				String origName  = this.columns[col].getOriginalName() ;
				String colName   = this.columns[col].getName() ;
				createRequest.append(String.format(CREATE_VIEW_STMT_COLUMN_DEF,
						tableName, origName, colName)) ;
				createRequest.append(CREATE_VIEW_STMT_COLUMN_DEF_SEPARATOR) ;
			}// while
			int requestLength = createRequest.length() ;
			createRequest.delete(
					requestLength - CREATE_VIEW_STMT_COLUMN_DEF_SEPARATOR.length(), 
					requestLength) ;
			
			createRequest.append(this.fromStmt) ;
			
			if (this.whereStmt != null) {
				String where = String.format(CREATE_VIEW_WHERE_STMT, this.whereStmt) ;
				createRequest.append(where) ;
			}// if
			
			createRequest.append(CREATE_VIEW_STMT_END) ;
			return createRequest.toString() ;
		}// getCreateViewSQL()
		
		/**
		 * Retourne l'indice de la colonne demandée.
		 * 
		 * @param 	columnName
		 * 			nom de la colonne dont on recherche l'indice
		 * 
		 * @return	indice de la colonne recherchée ou
		 * 			{@code DatabaseConstants#UNKOWN_COLUMN_INDEX} si la colonne
		 * 			n'existe pas dans cette table
		 */
		public int getColumnIndex(String columnName) {
			int nColumns = this.columns.length ;
			int index = 0 ;
			while ((index < nColumns) 
					&& !this.columns[index].getName().equals(columnName)) {
				index++ ;
			}// while
			if (index >= nColumns) {
				index = UNKOWN_COLUMN_INDEX ;
			}// if
			return index ;
		}// getColumnIndex(String)
		
		/**
		 * Retourne le nom de la vue.
		 * 
		 * @return	Le nom de la vue
		 */
		public String getName() {
			return this.name ;
		}// getName()
		
		/**
		 * Retourne la liste des noms des colonnes de la vue.
		 * 
		 * @return	Retourne la liste des noms des colonnes de la vue
		 */
		public String[] getColumnNames() {
			String[] names = new String[this.columns.length] ;
			int index = 0 ;
			for(ViewColumn col : this.columns) {
				names[index] = col.getName() ;
				index++ ;
			}// for
			return names ;
		}// getColumnNames()
 		
	}// class SQLView
	
	//TODO à documenter
	public static class ViewColumn {
		
		private String tableName ;
		private String originalName ;
		private String name ;
		
		/**
		 * @param tableName
		 * @param originalName
		 * @param name
		 */
		public ViewColumn(String tableName, String originalName, String name) {
			super();
			this.tableName = tableName;
			this.originalName = originalName;
			this.name = name;
		}

		public String getTableName() {
			return this.tableName;
		}

		public String getOriginalName() {
			return this.originalName;
		}

		public String getName() {
			return this.name;
		}
		
	}// class ViewColumn
	
	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *\
	 *                                                                                   *
	 * 		Autres                                                                       *
	 *                                                                                   * 
	\* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

	public static final SQLTable[] ALL_TABLES = new SQLTable[]{
		TABLE_SCHEDULE, TABLE_LESSON, TABLE_LESSON_HISTORY, TABLE_STUDENT_NUMBER
	} ;
	
	public static final SQLView[] ALL_VIEWS = new SQLView[]{
		VIEW_LESSON_WITH_HISTORY
	} ;

}// interface DatabaseConstants